<?php
class MysqliDb
{

    /**
     * @var private $_host 
     */
    private $_host = '127.0.0.1';

    /**
     * @var private $_username 
     */
    private $_username = 'root';

    /**
     * @var private $_password 
     */
    private $_password = '123456';

    /**
     * @var private $_port 
     */
    private $_port = '3306';

    /**
     * @var private $_db 
     */
    private $_db = 'test';

    /**
     * @var private $_charset 
     */
    private $_charset = 'utf8';

    /**
     * @var private $_mysqli 
     */
    private $_mysqli = '';

    /**
     * @var private $_resourceSql 
     */
    private $_resourceSql = '';

    /**
     * @var private $_prepareSql 
     */
    private $_prepareSql = '';

    /**
     * @var private $_resourceData 
     */
    private $_resourceData = '';

    /**
     * @var private $_typeStr 
     */
    private $_typeStr = '';

    /**
     * @var private $_stmt 
     */
    private $_stmt = '';

    /**
     * @var private $_errno 
     */
    private $_errno = 0;

    /**
     * @var private $_error 
     */
    private $_error = 'success';

    /**
     * @var private $_typeKey 
     */
    private $_typeKey = array('i', 'd', 's', 'b');

    /**
     * @var private $_typeValue 
     */
    private $_typeValue = array('is_int', 'is_float', 'is_string', 'is_bool');
    
    
    /**
     * @param string $host
     * @param string $username
     * @param string $password
     * @param string $db
     * @param int $port
     */
    public function __construct($config=null)
    {
        if (is_file($config)) {
            include $config;
        }
        isset($config['host']) && $this->_host = $config['host'];
        isset($config['username']) && $this->_username = $config['username'];
        isset($config['password']) && $this->_password = $config['password'];
        isset($config['db']) && $this->_db = $config['db'];
        isset($config['port']) && $this->_port = $config['port'];
        isset($config['charset']) && $this->_charset = $config['charset'];
        $this->connect();
    }


    /**
     * connect mysql 
     * 
     * @desc
     * 
     * @access public
     * @return void
     * @exception none
     */
    public function connect()
    {
        if (!$this->_mysqli || !$this->_mysqli->ping()) {
            $this->_mysqli = new mysqli ($this->_host, $this->_username, $this->_password, $this->_db, $this->_port)
            or die('There was a problem connecting to the database');
        }
        $this->_mysqli->set_charset ('utf8');
    }
  

    /**
     * execute sql query 
     * 
     * @desc
     * 
     * @access public
     * @param $sql
     * @param $data=array(
     * @return void
     * @exception none
     */
    public function runSql($sql, $data=array())
    {
        $this->_resourceSql = $sql;
        $this->_resourceData = $data;
        $this->_stmtQuery();
        return ($this->_stmt && $this->_stmt->affected_rows > 0 ? $this->_stmt->insert_id : false);
    }


    /**
     * get all data  
     * 
     * @desc
     * 
     * @access public
     * @param $sql
     * @param $data=array(
     * @return void
     * @exception none
     */
    public function getData($sql, $data=array())
    {
        $this->_resourceSql = $sql;
        $this->_resourceData = $data;
        if (false === $this->_stmtQuery()) {
            return false;
        } 
        $parameters = array();
        $results = array();
        $meta = $this->_stmt->result_metadata();
        if(!$meta && $this->_stmt->sqlstate) { 
            return array();
        }
        $row = array();
        while ($field = $meta->fetch_field()) {
            $row[$field->name] = null;
            $parameters[] = &$row[$field->name];
        }
        // avoid out of memory bug in php 5.2 and 5.3
        version_compare(phpversion(), '5.4', '<') && $this->_stmt->store_result();
        call_user_func_array(array($this->_stmt, 'bind_result'), $parameters);
        
        while ($this->_stmt->fetch()) {
            $x = array();
            foreach ($row as $key => $val) {
                $x[$key] = $val;
            }
            array_push($results, $x);
        }
        $this->_stmt->free_result();
        return $results;
    }
   

    /**
     * get a line data 
     * 
     * @desc
     * 
     * @access public
     * @param $sql
     * @param $data=array(
     * @return void
     * @exception none
     */
    public function getLine($sql, $data=array())
    {
        $data = $this->getData($sql, $data);
        if (!$data) {
            return false;
        }
        return reset($data);
    }


    /**
     * get a var from sql data 
     * 
     * @desc
     * 
     * @access public
     * @param $sql
     * @param $data=array(
     * @return void
     * @exception none
     */
    public function getVar($sql, $data=array())
    {
        $data = $this->getLine($sql, $data);
        if (!$data) {
            return false;
        }
        return $data[reset(array_keys($data))];

    }


    /**
     * get a var array from sql data 
     * 
     * @desc
     * 
     * @access public
     * @param $sql
     * @param $data=array(
     * @return void
     * @exception none
     */
    public function getVarArray($sql, $data=array())
    {
        $data = $this->getData($sql, $data);
        if (!$data) {
            return false;
        }
        return array_map(function($v){return reset($v);}, $data);
    }


    /**
     * get last id 
     * 
     * @desc
     * 
     * @access public
     * @return void
     * @exception none
     */
    public function lastId()
    {
        return empty($this->_stmt) ? $this->_mysqli->insert_id : $this->_stmt->insert_id;
    }


    /**
     * get count rows 
     * 
     * @desc
     * 
     * @access public
     * @return void
     * @exception none
     */
    public function countRows()
    {
        return $this->_stmt->num_rows;
    }
   

    /**
     * get affected rows 
     * 
     * @desc
     * 
     * @access public
     * @return void
     * @exception none
     */
    public function affectedRows()
    {
        return empty($this->_stmt) ? $this->_mysqli->affected_rows : $this->_stmt->affected_rows;
    }


    /**
     * escape str from sql 
     * 
     * @desc
     * 
     * @access public
     * @param $str
     * @return void
     * @exception none
     */
    public function escape($str)
    {
        return $this->_mysqli->real_escape_string($str);
    }


    /**
     * check mysql ping status 
     * 
     * @desc
     * 
     * @access public
     * @return void
     * @exception none
     */
    public function ping()
    {
        return $this->_mysqli->ping();
    }


    /**
     * begin auto commit of transaction 
     * 
     * @desc
     * 
     * @access public
     * @return void
     * @exception none
     */
    public function beginTrans()
    {
        return $this->_mysqli->autocommit(true);
    }


    /**
     * commit a transaction 
     * 
     * @desc
     * 
     * @access public
     * @return void
     * @exception none
     */
    public function commitTrans()
    {
        return $this->_mysqli->commit();
    }


    /**
     * rollback transaction 
     * 
     * @desc
     * 
     * @access public
     * @return void
     * @exception none
     */
    public function rollbackTrans()
    {
        return $this->_mysqli->rollback();
    }


    /**
     * select a database db 
     * 
     * @desc
     * 
     * @access public
     * @param $dbname
     * @return void
     * @exception none
     */
    public function selectDb($dbname)
    {
        return $this->_mysqli->select_db($dbname);
    }


    /**
     * set mysql link charset 
     * 
     * @desc
     * 
     * @access public
     * @param $charset
     * @return void
     * @exception none
     */
    public function setCharset($charset)
    {
        return $this->_mysqli->set_charset($charset);
    }


    /**
     * get error 
     * 
     * @desc
     * 
     * @access public
     * @return void
     * @exception none
     */
    public function getErrno()
    {
        return $this->_errno;
    }


    /**
     * get errno 
     * 
     * @desc
     * 
     * @access public
     * @return void
     * @exception none
     */
    public function getError()
    {
        return $this->_error;
    }


    /**
     * get mysqli stmt query resource 
     * 
     * @desc
     * 
     * @access private
     * @return void
     * @exception none
     */
    private function _stmtQuery()
    {
        if (false === $this->_checkParams()) {
            $this->_setError(20001, "params error");
            return false;
        }
        $this->_buildSql();
        $this->_prepareSql();
        if (false === $this->_stmt) {
            return false;
        }
        if ($this->_resourceData  && is_array($this->_resourceData)) {
            call_user_func_array(array($this->_stmt, 'bind_param'), $this->_refValues());
        }
        $this->_stmt->execute();
        0 !== $this->_stmt->errno && $this->_setError($this->_stmt->errno, $this->_stmt->error);
    }


    /**
     * create prepare sql 
     * 
     * @desc
     * 
     * @access private
     * @return void
     * @exception none
     */
    private function _prepareSql()
    {
        if ($this->_lastPrepareSql === $this->_prepareSql && $this->_stmt) {
            $this->_lastPrepareSql = $this->_prepareSql;
            $this->_stmt = $this->_mysqli->prepare($this->_prepareSql);
            0 !== $this->_mysqli->errno && $this->_setError($this->_mysqli->errno, $this->_mysqli->error);
        }
    }


    /**
     * build a sql for mysqli prepare 
     * 
     * @desc
     * 
     * @access private
     * @return void
     * @exception none
     */
    private function _buildSql()
    {
        $this->_prepareSql = preg_replace('/\s?(\?)(\w+)\s?/', '\\1\\3', $this->_resourceSql);
    }


    /**
     * check sql params 
     * 
     * @desc
     * 
     * @access private
     * @return void
     * @exception none
     */
    private function _checkParams()
    {
        if ( false === strpos($this->_resourceSql, "?")) {
            return true;
        }
        preg_match_all('/\s?\?([a-z])?(\d+)?\s?/', $this->_resourceSql, $matches);
        extract(array_combine($this->_typeKey, $this->_typeValue));
        $this->_typeStr = '';
        foreach ($matches[1] as $key=>$type) {
            if ($type == 'i' && intval($matches[2][$key]) < $this->_resourceData[$key]) {
                return false; 
            } elseif (in_array($type, array('s', 'b')) && $matches[2][$key] < strlen($this->_resourceData[$key])) {
                return false; 
            } elseif (!in_array($type, $this->_typeKey)) {
                return false; 
            }
            true == $$type($this->_resourceData[$key]) && $this->_typeStr .= $type;
        }
        return strlen($this->_typeStr) === count($this->_resourceData) ? $this->_typeStr : false; 
    }


    /**
     * bind params 
     * 
     * @desc
     * 
     * @access private
     * @return void
     * @exception none
     */
    private function _refValues()
    {
        $arr = array_merge(array($this->_typeStr), $this->_resourceData);
        if (strnatcmp(phpversion(), '5.3') >= 0) {
            $refs = array();
            foreach ($arr as $key=>$value) {
                $refs[$key] = &$arr[$key];
            }
            return $refs;
        }
        return $arr;
    }


    /**
     * set or reset error 
     * 
     * @desc
     * 
     * @access private
     * @param $errno=0
     * @param $error='success'
     * @return void
     * @exception none
     */
    private function _setError($errno = 0, $error = 'success')
    {
        $this->_errno = $errno;
        $this->_error = $error;
    }


    /**
     * close mysqli db resource 
     * 
     * @desc
     * 
     * @access public
     * @return void
     * @exception none
     */
    public function __destruct()
    {
        if ($this->_mysqli) {
            $this->_mysqli->close();
        }
    }

} // END class
